#!/user/bin/evn Python3 
# -*- coding:utf-8 -*-
# project:PythonForWork
# SqliteDateToFile.py
# 将Sqlite数据/db/查询结果导出成 excel/csv文件
# author:ZhaoHeXin
# creation time:2019/11/29--15:44

import os
import DaPy as dp
from core.Management import SqliteManagement
from core.OsOperate import OsOperate
from core.Decorator import timer


class SqliteToFile(object):
    def __init__(self, path, filepath):
        """
        接收必要参数
        :param path: 数据库绝对路径
        :param filepath: 存储文件路径,excel需要制定具体文件,csv需要制定相对目录
        """
        self.path = path
        self.filepath = filepath

    @timer
    def output_db_excel(self, tables=None):
        """
        将Db导出为excel文件
        :return: 返回True/False
        """
        print("------正在导出中，请稍后------")
        sheet = dp.read(self.path)
        sheet.save(self.filepath)
        print("------导出完成------")
        return True

    @timer
    def output_db_csv(self, tables=None):
        """
        将db中所有内容转换为csv文件后存储到对应目录文件中
        :table_list:指定导出表名,列表形式，不输入代表全部导出
        :return:返回执行结果
        """
        path = os.path.join(self.filepath, 'output_csv')
        file_operate = OsOperate(path)
        # 创建文件夹
        file_operate.creat_dir()
        # 删除当前文件夹内所有文件
        file_operate.del_file(True)
        # 写入文件
        with SqliteManagement(self.path) as f:
            table_list = [i[0] for i in f.execute("select name from sqlite_master where type = 'table'")]
            if tables is None:
                tables = table_list
            for table in table_list:
                # 判断是否为空表
                count = [i[0] for i in f.execute(f"select count(1) from {table}")][0]
                if count != 0 and table in tables:
                    # 获取标题信息
                    title = ",".join([i[1] for i in f.execute(f"pragma table_info({table})")])
                    # 获取数据
                    data_list = f.execute(f"select {title} from {table}")
                    # 写入文件
                    with open(os.path.join(self.filepath, "output_csv", f"{table}.csv"), "a+")as e:
                        e.write(f"{title}\n")
                        for data in data_list:
                            da = ",".join([str(i) for i in data])
                            e.write(f"{str(da)}\n")
        return True

    def output_date_excel(self):
        pass

    def output_date_csv(self):
        pass

    def input_excel(self):
        pass

    def input_csv(self):
        pass


if __name__ == '__main__':
    db_path = r"E:\飞秋接收\赵兴盼(94C69164BD49)(192.168.1.102)\2019-11-29 09_53_06\LDL2019_release(16)\assets\db\init.db"
    # file_path = r"C:\Users\ZhaoHeXin\Desktop\Ts.xls"
    file_path = r"C:\Users\ZhaoHeXin\Desktop"
    output = SqliteToFile(db_path, file_path)
    # output.output_db_excel()
    output.output_db_csv()
    pass
